const pool = require('../database/conn')

const getFriendsByUid = async (uid) =>{
    const sql = `SELECT id, name, avatar FROM users WHERE id in (SELECT friendships.fid FROM \`friendships\` JOIN users on friendships.uid = users.id where uid = ? and status = 1 UNION SELECT friendships.uid FROM users JOIN friendships ON friendships.fid = users.id WHERE friendships.fid = ?)`
    const [rows] = await pool.query(sql, [uid, uid])

    return rows
}

const getFriendsIdByUid = async (uid) => {
    const sql = 'SELECT friendships.fid FROM `friendships` JOIN users on friendships.uid = users.id where uid = ? and status = 1 UNION SELECT friendships.uid FROM users JOIN friendships ON friendships.fid = users.id WHERE friendships.fid = ?'
    const [rows] = await pool.query(sql, [uid, uid])
    return rows
}

const createNewFriendRequest = async (sender_id, receiver_id) => {
    const sql = 'INSERT INTO `friendrequest` (sender_id, receiver_id) VALUES(?,?)'
    const result = await pool.query(sql, [sender_id, receiver_id])
    return result
}

const getFriendRequest = async (sender_id, receiver_id) => {
    const sql = 'SELECT count(*) count FROM `friendrequest` where sender_id = ? and receiver_id = ? and isAccept = 0'
    const [result] = await pool.query(sql, [sender_id, receiver_id])
    return result
}

const getFriendRequestById = async (uid) => {
    const sql = 'SELECT friendrequest.*, users.`name` sender_name, users.avatar sender_avatar FROM `friendrequest` JOIN `users` ON users.id = sender_id WHERE receiver_id = ?'
    const [rows] = await pool.query(sql, [uid])
    return rows
}

const checkRelationshipByUid = async (uid_1, uid_2) => {
    const sql = 'SELECT count(*) count FROM `friendships` where (uid = ? or uid = ?) and (fid = ? or fid = ?)'
    const [result] = await pool.query(sql, [uid_1, uid_2, uid_1, uid_2])
    return result
}

const setFriendshipByUid = async (receiver_id, sender_id, dm_id) => {
    const sql = 'INSERT INTO `friendships` (uid, fid) VALUES(?,?);UPDATE `friendrequest` SET isAccept = 1 WHERE receiver_id = ? and sender_id = ?; INSERT INTO `direct_message` (id) VALUES(?); INSERT INTO `dm_members` (conversation_id, user_id) VALUES (?, ?); INSERT INTO `dm_members` (conversation_id, user_id) VALUES (?, ?);'
    const [result] = await pool.query(sql, [receiver_id, sender_id, receiver_id, sender_id, dm_id, dm_id, sender_id, dm_id, receiver_id])
    return result
}
const ignoreFriendRequestByUid = async (receiver_id, sender_id) => {
    const sql = 'UPDATE `friendrequest` SET isAccept = 1 WHERE receiver_id = ? and sender_id = ?'
    const [result] = await pool.query(sql, [receiver_id, sender_id])
    return result
}

const checkFriendReqStatusByUid = async (sender_id, receiver_id) => {
    const sql = 'SELECT count(*) count FROM `friendrequest` WHERE sender_id = ? and receiver_id = ? and isAccept = 0'
    const [row] = await pool.query(sql, [sender_id, receiver_id])
    return row
}

const getConversationsByUid = async (uid) => {
    const sql = 'SELECT `dm_members`.conversation_id, `users`.id, `users`.name, `users`.avatar FROM `dm_members` JOIN users on `dm_members`.user_id = `users`.id WHERE conversation_id in (SELECT conversation_id FROM `dm_members` where user_id = ?) and user_id != ?'
    const [row] = await pool.query(sql, [uid, uid])
    return row
}

module.exports = {
    getFriendsByUid,
    getFriendsIdByUid,
    createNewFriendRequest,
    getFriendRequestById,
    checkRelationshipByUid,
    setFriendshipByUid,
    ignoreFriendRequestByUid,
    checkFriendReqStatusByUid,
    getConversationsByUid,
    getFriendRequest
}